/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dao.exception.DAONoteException;
import dao.interfaces.DAONote;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import model.Etudiant;
import model.Note;

/**
 *
 * @author Paul
 */
public class DAONoteImpl implements DAONote {

    private DAOInit daoConnection;

    public DAONoteImpl(DAOInit daoConnection) {
        this.daoConnection = daoConnection;
    }

    public void create(float note, int idEtudiant, int idExamen) throws DAONoteException {
        String query = "insert into note (id_examen,id_eleve,note) values (?,?,?)";//preparation de la requete
        /**
         * initialisation des variables *
         */
        int nb = 0;
        int autoIncKeyFromApi = -1;
        PreparedStatement ps = null;

        /**
         * lancement de la connexion à la bd et de la requete *
         */
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete

            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, idExamen);
            ps.setInt(2, idEtudiant);
            ps.setFloat(3, note);


            nb = ps.executeUpdate();//lancement de la requête
            ResultSet rs = ps.getGeneratedKeys();//recupération du résultat

            if (rs.next()) {//récuperation de l'id 
                autoIncKeyFromApi = rs.getInt(1);
                System.out.println(autoIncKeyFromApi);
            }
        } catch (SQLException e) {
            throw new DAONoteException("Impossible d'affecter une note déjà existante");
        }

    }

    public boolean update(Note obj) {
        String query = "UPDATE `note` SET `id_eleve`=?,`id_examen`=?,`note`= ? WHERE `id_eleve` = ? AND `id_examen` = ? ";
        int nb = 0;
        PreparedStatement ps = null;
        boolean del = false;
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, obj.getIdEleve());
            ps.setInt(2, obj.getIdExamen());
            ps.setFloat(3, obj.getNote());
            ps.setInt(4, obj.getIdEleve());
            ps.setInt(5, obj.getIdExamen());
            nb = ps.executeUpdate();//lancement de la requête
            if (nb >= 1) {
                del = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return del;
    }

    public boolean delete(Note obj) {
        boolean del = false;
        String query = "DELETE FROM note WHERE id_eleve = ? AND id_examen = ?";
        int nb = 0;
        PreparedStatement ps = null;
        try {
            Connection conn = daoConnection.getConnection();//Creation de la connexion
            ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);//preparation de la requete
            /**
             * remplacement des points d'interogation dans la query par les
             * variables*
             */
            ps.setInt(1, obj.getIdEleve());
            ps.setInt(2, obj.getIdExamen());
            nb = ps.executeUpdate();//lancement de la requête
            if (nb >= 1) {
                del = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return del;
    }

    public ArrayList<Note> getAll() {
        String query = "";
        return findGeneral(query);
    }

    public Note find(int id) {
        String query = "";
        Note note = null;
        note = findGeneral(query).get(0);
        return note;
    }

    /**
     * retourne la note d'un exam d'un étudiant
     *
     */
    public Note findNoteEtudiantExam(int id_eleve, int id_exam) throws DAONoteException{
        ArrayList<Note> notes = new ArrayList<Note>();
        String query = "select * from note where id_eleve=" + id_eleve + " and id_examen='" + id_exam + "';";
        Note note = null;

        notes = findGeneral(query);
        if (notes.size() >= 1) {
            return notes.get(0);
        }
        return null;
    }

//---------------------------------------------------------private method--------------------------------------------------------------------//
    private int changeGeneral(String query) {
        Statement st = null;
        int nb = 0;
        try {
            Connection conn = daoConnection.getConnection();
            st = conn.createStatement();
            nb = st.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return nb;
    }

    private ArrayList<Note> findGeneral(String requete) {
        ArrayList<Note> notes = new ArrayList<Note>();
        Statement st = null;
        Note note = null;

        try {
            Connection conn = daoConnection.getConnection();
            st = conn.createStatement();
            ResultSet res = st.executeQuery(requete);
            while (res.next()) {
                note = createObject(res);
                notes.add(note);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return notes;
    }

    private Note createObject(ResultSet res) {
        Note note = null;
        try {
            Float notec = res.getFloat("note");
            note = new Note(notec);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return note;
    }
}
